<?php
    class APaymentRevenue extends CActiveRecord{
        public static function model($className = __CLASS__) {
            return parent::model ( $className );
        }
        
        public function getDataConfirm($userId,$year){
            $condition="";
            $condition.= !empty($userId)? " AND userId= ".$userId : "";
            if(!empty($year)){
                $condition.=" AND year= ".$year;
            } else{
                $condition=" ";
            }
            
            $sql="SELECT `userId`,`month`,`year`,`money` FROM c_payment_confirm WHERE 1 ".$condition;
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;
        }
        public function getDataConfirmLastMonth($userId,$year,$month){
            $condition="";
            $condition.= !empty($userId)? $condition.=" AND userId= ".$userId : "";
            if(!empty($year)){
                $condition.=" AND year= ".$year;
            } else{
                $condition=" ";
            }
            if(!empty($year)){
                $condition.=" AND month= ".$month;
            } else{
                $condition=" ";
            }
            $sql="SELECT `userId`,`month`,`year`,`money` FROM c_payment_confirm WHERE 1 ".$condition;
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;
        }
        public function getUserId($username)
        {
            $sql = "SELECT id FROM c_user WHERE `username`='" . mysql_escape_string($username) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows;
        }
        public function getUserRefcode($refCode)
        {
            $sql = "SELECT id FROM c_user WHERE `refCode`='" . mysql_escape_string($refCode) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows;
        }
        public function getAllRate()
        {   
            $condition=" ";
            /*if(!empty($year)){
                $condition.=" AND year= ".$year;
            } else{
                $condition=" ";
            }*/
            $sql = "SELECT * FROM c_rate WHERE 1".$condition;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            $rate = array();
            foreach($rows as $row){
                $rate[$row["month"] . "_" . $row["year"]][$row["type"]]["percent_wapmaster"] = $row["rate_of_user"];

            }

            return $rate;
        }

        public function getAllRateSms()
        {   
            $condition=" ";
            /*if(!empty($year)){
                $condition.=" AND year= ".$year;
            } else{
                $condition=" ";
            }*/
            $sql = "SELECT * FROM c_rate_sms WHERE 1".$condition;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            $rate = array();
            foreach($rows as $row){
                $rate[$row["month"] . "_" . $row["year"]][$row["serviceNumber"]] = $row["rate"];            
            }

            return $rate;    
        }
        public function getAllRateCard()
        {   
            $condition=" ";
            $sql = "SELECT * FROM c_rate_card WHERE 1".$condition;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            $rate = array();
            foreach($rows as $row){
                $rate[$row['month']][$row["year"]] = $row["rate"];            
            }

            return $rate;    
        }

        public function getAllDataSms($userId,$year){
            $condition="";
            $condition.= !empty($userId)? $condition.=" AND userId= ".$userId : "";
            if(!empty($year)){
                $condition.=" AND year= ".$year;
            } else{
                $condition=" ";
            }
            $sql="SELECT count(price) as count,`userId`,`type`,`month`,`year`,`price`,`percent_wapmaster`,`serviceNumber` FROM c_sms_log WHERE 1 AND status=1 ".$condition." GROUP BY  `month`, price, `type`, serviceNumber,`percent_wapmaster`";
           // echo $sql;die;
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;
        }
        public function getAllDataCard($userId,$year){
            $condition="";
            $condition.= !empty($userId)? $condition.=" AND userId= ".$userId : "";
            if(!empty($year)){
                $condition.=" AND year= ".$year;
            } else{
                $condition=" ";
            }
            $sql="SELECT count(id) as count, `userId`,`type`,`month`,`year`,`price`,`percent_wapmaster` FROM c_card_log WHERE 1 AND status=1 ".$condition." GROUP BY  `month`, price, `type`,`percent_wapmaster`";
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;
        }
        public function getAllDataCharging($userId,$year){
            $condition="";
            $condition.= !empty($userId)? $condition.=" AND userId= ".$userId : "";
            if(!empty($year)){
                $condition.=" AND year= ".$year;
            } else{
                $condition=" ";
            }
            $sql="SELECT count(id) as count, `userId`,`month`,`year`,`price_wapmaster`,`price_telco` FROM c_wapcharging_log WHERE 1 AND status=1 ".$condition." GROUP BY  `month`, price_wapmaster, `price_telco`";
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;
        }
        
        public function getAllDataSmsYear($userId,$year){
            $condition="";
            $condition.= !empty($userId)? $condition.=" AND userId= ".$userId : "";
            
            $sql="SELECT count(price) as count,`userId`,`type`,`month`,`year`,`price`,`percent_wapmaster`,`serviceNumber` FROM c_sms_log WHERE 1 AND status=1 ".$condition." AND year <= ".$year." GROUP BY  `month`, price, `type`, serviceNumber,`percent_wapmaster`";
           // echo $sql."</br>";
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;
        }
        public function getAllDataCardYear($userId,$year){
            $condition="";
            $condition.= !empty($userId)? $condition.=" AND userId= ".$userId : "";
            
            $sql="SELECT count(id) as count, `userId`,`type`,`month`,`year`,`price`,`percent_wapmaster` FROM c_card_log WHERE 1 AND status=1 ".$condition." AND year <= ".$year." GROUP BY  `month`, price, `type`,`percent_wapmaster`";
            
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;
        }
        public function getAllDataChargingYear($userId,$year){
            $condition="";
            $condition.= !empty($userId)? $condition.=" AND userId= ".$userId : "";
            
            $sql="SELECT count(id) as count, `userId`,`month`,`year`,`price_wapmaster` FROM c_wapcharging_log WHERE 1 AND status=1 ".$condition." AND year <= ".$year." GROUP BY  `month`,`price_wapmaster`";
            
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;
        }
        public function getDataConfirmYear($userId,$year){
            $condition="";
            $condition.= !empty($userId)? $condition.=" AND userId= ".$userId : "";
            $from_date="2012-2-1";
            $from_date=strtotime($from_date);
            $to_date=$year."-1-31 23:59:59";
            $to_date=strtotime($to_date);
            $sql="SELECT `userId`,`month`,`year`,`money` FROM c_payment_confirm WHERE  create_date >='$from_date' AND create_date<='$to_date'".$condition;
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;
        }
        

    }
?>
